from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.utils.cell import get_column_letter, column_index_from_string
from io import BytesIO
from copy import copy
from re import findall
try:
    import requests
except:
    requests = None


class TemplateImage:
    def __init__(self, ws, file):
        self.ws = ws
        if isinstance(file, str) and (file.startswith('http') or file.startswith('https')):
            if requests is None:
                raise ImportError("请先通过安全requests")
            try:
                file = requests.get(url=file, timeout=20).content
            except Exception as e:
                raise ValueError(e)
        if isinstance(file, BytesIO):
            self.data = file
            self.data.seek(0)
        elif isinstance(file, bytes):
            self.data = BytesIO(file)
        else:
            raise TypeError("传入类型应该为http:// 或 https:// 或 bytes 或 BytesIO()")
        self.image = Image(self.data)
        self._anchor = None

    def set_width(self, width):
        self.image.width = width

    def set_height(self, height):
        self.image.height = height

    def anchor(self, anchor):
        self._anchor = anchor

    @staticmethod
    def check(string):
        if isinstance(string, str) and (string.startswith('http') or string.startswith('https')):
            return True
        if isinstance(string, (BytesIO, bytes)):
            return True

    def save(self):
        # 调整单元格大小适配图片
        if self._anchor:
            self.image.anchor = self._anchor
        self.ws.add_image(self.image)


class TemplateExcel:
    def __init__(self, data: dict, template_path):
        if not isinstance(data, dict):
            raise ValueError("传入的数据必须是个字典")
        self.data = data
        self.wb = load_workbook(template_path)
        self.ws = self.wb.active

    def get_merged_cell_top_left(self, row: int, col: int):
        """
        检查单元格是否属于合并区域，如果是，返回合并区域的左上角单元格坐标
        如果不是合并单元格，返回原坐标
        """
        for merged_range in self.ws.merged_cells.ranges:
            if merged_range.min_row <= row <= merged_range.max_row and \
                    merged_range.min_col <= col <= merged_range.max_col:
                return merged_range.min_row, merged_range.min_col
        return row, col

    def copy_row_style(self, source_row, target_row):
        """
        复制源行的样式到目标行

        参数:
            source_row: 源行号（整数，从1开始）
            target_row: 目标行号（整数，从1开始）
        """
        for cell in self.ws[source_row]:
            target_cell = self.ws.cell(row=target_row, column=cell.column)
            target_cell.font = copy(cell.font)
            target_cell.alignment = copy(cell.alignment)
            target_cell.border = copy(cell.border)
            target_cell.fill = copy(cell.fill)
            target_cell.number_format = cell.number_format
            target_cell.protection = copy(cell.protection)
            target_cell.value = copy(cell.value)

    def insert_data(self, index, row):
        """
        插入数据
        :param ws:
        :param index:第几行插入
        :param row: 插入几行
        :return:合并单元格列表数据
        """
        merged_cells_range = self.ws.merged_cells.ranges
        merged_array = []
        for merged_cell in merged_cells_range:
            merged_array.append({
                'min_col': merged_cell.min_col,
                'min_row': merged_cell.min_row,
                'max_col': merged_cell.max_col,
                'max_row': merged_cell.max_row,
            })
        for cell in merged_array:
            if cell['min_row'] >= index:
                cell['min_row'] += row
            if cell['max_row'] >= index:
                cell['max_row'] += row
        for i in range(row):
            self.ws.insert_rows(index)
            current = index + i
            self.copy_row_style(current + 1, index)
            for cell in [cell for cell in merged_array if
                         cell['min_row'] == index + row and cell['max_row'] == index + row]:
                merged_array.append({
                    'min_col': cell['min_col'],
                    'min_row': current,
                    'max_col': cell['max_col'],
                    'max_row': current,
                })
        merged_cells_range.clear()
        for cell in merged_array:
            s = '%s%s:%s%s' % (
                get_column_letter(cell['min_col']), cell['min_row'], get_column_letter(cell['max_col']),
                cell['max_row'])
            self.ws.merge_cells(s)

    def get_merged_range(self, cell_coord):
        """
        获取单元格所在的合并区域范围（若为普通单元格则返回自身范围）

        参数:
            cell_coord: 单元格坐标（如"A1"）
        返回:
            dict: 包含min_row, max_row, min_col, max_col的字典
        """
        col_str = ''.join([c for c in cell_coord if c.isalpha()])
        row = int(''.join([c for c in cell_coord if c.isdigit()]))
        col = column_index_from_string(col_str)
        for merged_range in self.ws.merged_cells.ranges:
            if (merged_range.min_row <= row <= merged_range.max_row and
                    merged_range.min_col <= col <= merged_range.max_col):
                return {
                    "min_row": merged_range.min_row,
                    "max_row": merged_range.max_row,
                    "min_col": merged_range.min_col,
                    "max_col": merged_range.max_col,
                    "top_left": f"{get_column_letter(merged_range.min_col)}{merged_range.min_row}"
                }

        return {
            "min_row": row,
            "max_row": row,
            "min_col": col,
            "max_col": col,
            "top_left": cell_coord
        }

    def calculate_range_size(self, merged_range):
        """
        计算合并区域的总宽度（像素）和总高度（像素）

        参数:
            merged_range: 由get_merged_range返回的合并区域字典
        返回:
            tuple: (总宽度像素, 总高度像素)
        """
        total_width = 0
        for col in range(merged_range["min_col"], merged_range["max_col"] + 1):
            col_letter = get_column_letter(col)
            col_width = self.ws.column_dimensions[col_letter].width or 8.43
            total_width += col_width * 7

        total_height = 0
        for row in range(merged_range["min_row"], merged_range["max_row"] + 1):
            row_height = self.ws.row_dimensions[row].height or 15
            total_height += row_height * 1.333

        return round(total_width), round(total_height)

    def render_data(self):
        """
        渲染基础数据
        :return:
        """
        array_tasks = {}
        images_coordinates = {}
        for row in range(1, self.ws.max_row):
            for col in range(1, self.ws.max_column):
                actual_row, actual_col = self.get_merged_cell_top_left(row, col)
                cell_value = self.ws.cell(row=actual_row, column=actual_col).value
                if not cell_value or not isinstance(cell_value, str): continue
                if arr := findall(r'\{(.+?)\}', cell_value):
                    expr = arr[0]
                    if '.' in expr:
                        v1, v2 = expr.split('.', 1)
                        array = self.data.get(v1)
                        if array and isinstance(array, list):
                            # 记录任务
                            array_tasks.setdefault(actual_row, len(array) - 1)
                    else:
                        obj = {}
                        flag = None
                        for expr in arr:
                            new_value = self.data.get(expr)
                            obj[expr] = new_value
                            if TemplateImage.check(new_value):
                                flag = new_value
                        if flag:
                            if images_coordinates.get((actual_row, actual_col)):
                                continue
                            merged_range = self.get_merged_range('%s%s' % (get_column_letter(actual_col), actual_row))
                            img_width, img_height = self.calculate_range_size(merged_range)
                            images_coordinates[(actual_row, actual_col)] = flag
                            image = TemplateImage(self.ws, flag)
                            image.set_width(img_width)
                            image.set_height(img_height)
                            image.anchor(merged_range['top_left'])
                            image.save()
                        elif obj:
                            self.ws.cell(row=actual_row, column=actual_col).value = cell_value.format(**obj)
        return array_tasks

    def render_list_data(self):
        """
        渲染列表数据
        :param ws:
        :param data:
        :return:
        """
        data = copy(self.data)
        obj = {}
        for row in range(1, self.ws.max_row + 1):
            for col in range(1, self.ws.max_column + 1):
                actual_row, actual_col = self.get_merged_cell_top_left(row, col)
                cell_value = self.ws.cell(row=actual_row, column=actual_col).value
                if not cell_value or not isinstance(cell_value, str): continue
                if arr := findall(r'\{(.+?)\}', cell_value):
                    expr = arr[0]
                    if '.' not in expr: continue
                    v1, v2 = expr.split('.', 1)
                    array = data.get(v1)
                    if isinstance(array, list):
                        if obj.get(v1):
                            c, r = obj[v1]
                            if r != row:
                                obj[v1] = (c + 1, row)
                        else:
                            obj[v1] = (0, row)
                        c, r = obj[v1]
                        try:
                            value = data[v1][c].get(v2)
                        except KeyError:
                            obj[v1] = (0, row)
                            value = data[v1][0].get(v2)
                        except IndexError:
                            self.ws.cell(row=actual_row, column=actual_col).value = None
                            continue
                        if value is not None:
                            self.ws.cell(row=actual_row, column=actual_col).value = value

    def render(self, output_path):
        current_index = 0
        for index, row in self.render_data().items():
            self.insert_data(index + current_index, row)
            current_index += row
        self.render_list_data()
        self.wb.save(output_path)
        if isinstance(output_path, BytesIO):
            output_path.seek(0)
            return output_path

    def close(self):
        try:
            self.wb.close()
        except:
            pass

    def __del__(self):
        self.close()
